USE SAS
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetAccountOpportunities')
	BEGIN
		DROP  Procedure  dbo.GetAccountOpportunities
	END
GO

CREATE Procedure dbo.GetAccountOpportunities
	@accountGUID		VARCHAR(100)
,	@userId				VARCHAR(10) = '0'
AS
BEGIN

	;WITH A AS (
		-- OPPORTUNITY HEADER
		SELECT
			op.[Description]				AS OpportunityName
		,	op.OppRevenue					AS Revenue
		,	s.StatusDesc					as [Status]
		,	s.SortOrder						as [StatusOrder]
		,	op.OppGuid						AS PKID
		FROM TR_OPPORTUNITYH as op WITH (NOLOCK)
		INNER JOIN TR_Relationships as r
			ON r.SourceGUID = op.OppGuid
		LEFT OUTER JOIN TR_STATUS as s WITH (NOLOCK)
			on s.Status = op.Status		
		WHERE
			r.Partner2 = @userID
		AND r.RelationshipType = 3		-- OWNER
		AND op.Status = 3 -- IN PROGRESS
	), B AS (
		-- ACCOUNT
		SELECT
			acc.PartnerName					AS AccountName
		,	A.PKID
		FROM TR_PARTNER AS acc WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS AS r WITH (NOLOCK)
			ON r.PARTNER2 = acc.PartnerNo
		INNER JOIN A
			ON A.PKID = R.SourceGUID
		WHERE
			r.RelationshipType = 11 -- OPPORTUNITY PARTNER
		AND acc.PartnerGUID = @accountGUID
	), FINAL AS (
		SELECT
			A.OpportunityName			AS [Description]
		,	A.Status
		,	A.Revenue
		,	A.StatusOrder
		,	A.PKID
		FROM A
		INNER JOIN B
			ON B.PKID = A.PKID
	)

	SELECT 
		[Description]
	,	[Status]
	,	Revenue
	,	PKID
	FROM FINAL
	ORDER BY StatusOrder

END
GO

-- exec GetAccountOpportunities

-- select * from tr_partner order by 1
